package cn.strategy.fund;

import cn.math.MyStatistics;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 找到前段时间，跌的多，现在不怎么跌的
 * */
public class Mai_dian_case1 {
    @Test
    public void show_mai_dian() throws ClassNotFoundException, SQLException {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/data;AUTO_SERVER=TRUE", "root", "root");

        String sql = "SELECT symbol FROM type_stock GROUP BY symbol";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            //getData_ex(conn, rs.getString(1));
            getLastPrice(conn, rs.getString(1));
        }

        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        if (conn != null) conn.close();
    }

    public void getLastPrice(Connection conn, String symbol) {
        String sql = "SELECT PRICE, NAME, ZHANGDIE, STOCK_DATE FROM df_stock_date WHERE SYMBOL = '"+ symbol +"' ORDER BY STOCK_DATE DESC";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        Float last_price = 0F;
        Float last_zhangdie = 0F;
        List<Float> lst_price = new ArrayList<>();

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                if (last_price == 0F) {
                    last_price = rs.getFloat(1);
                    last_zhangdie = rs.getFloat(3);
                }
                // 判断 mai_dian
                Float vs = get_mai_dian(conn, symbol, (Float)rs.getFloat(1), rs.getString(4));
                if (vs != 0F) {
                    if (lst_price.size() <= 3 || MyStatistics.my_mean_f(lst_price) < 0.2) {
                        lst_price.add(rs.getFloat(1));
                    } else {
                        Float f = rs.getFloat(1);
                        Float v = f / last_price;
                        if (v < 1.01 && v > 0.99 && last_zhangdie > 0F && last_zhangdie < 0.5F) {
                            System.out.println(symbol + " " + rs.getString(2) + " " + rs.getString(3));
                        }
                    }
                } else {
                    lst_price.add(rs.getFloat(1));
                }
                //getLastPrice(conn, symbol, rs.getString(2), (Float)rs.getFloat(1), rs.getFloat(3));
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Float get_mai_dian(Connection conn, String symbol, Float price, String stock_date) {
        String sql = "SELECT PRICE FROM mai_dian WHERE SYMBOL = '"+ symbol +"' and h = 0 and STOCK_DATE = '"+ stock_date +"' ORDER BY STOCK_DATE DESC";

        Float vs = 0F;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                //price = rs.getFloat(1);
                //lst.add(rs.getFloat(1));
                vs = rs.getFloat(1);
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return vs;
    }

    public void judge(Connection conn, String symbol, String name, Float price, Float zhangdie) {
        String sql = "SELECT PRICE, STOCK_DATE FROM mai_dian WHERE SYMBOL = '"+ symbol +"' and h = 0 ORDER BY STOCK_DATE DESC";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        //List<Float> lst = new ArrayList<>();

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                //price = rs.getFloat(1);
                //lst.add(rs.getFloat(1));
                Float f = rs.getFloat(1);
                Float v = f / price;
                if (v < 1.01 && v > 0.99 && zhangdie > 0F && zhangdie < 0.5F) {
                    System.out.println(symbol + " " + name + " " + rs.getString(2));
                }
                break;
            }

//            if (lst.size() > 0) {
//                Float f = lst.get(0);
//                Float v = f / price;
//                if (v < 1.01 && v > 0.98) {
//                    System.out.println(symbol + " " + name);
//                }
//            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public void getLastPrice(Connection conn, String symbol, String name, Float price, Float zhangdie) {
        String sql = "SELECT PRICE, STOCK_DATE FROM mai_dian WHERE SYMBOL = '"+ symbol +"' and h = 0 ORDER BY STOCK_DATE DESC";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        //List<Float> lst = new ArrayList<>();

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                //price = rs.getFloat(1);
                //lst.add(rs.getFloat(1));
                Float f = rs.getFloat(1);
                Float v = f / price;
                if (v < 1.01 && v > 0.99 && zhangdie > 0F && zhangdie < 0.5F) {
                    System.out.println(symbol + " " + name + " " + rs.getString(2));
                }
                break;
            }

//            if (lst.size() > 0) {
//                Float f = lst.get(0);
//                Float v = f / price;
//                if (v < 1.01 && v > 0.98) {
//                    System.out.println(symbol + " " + name);
//                }
//            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}
